package org.zjx.db;

import com.alibaba.fastjson2.JSON;
import org.zjx.model.prop.ItemTemplate;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ItemTemplateDB {
    private static boolean isTableCreated = false;
    private final SQLiteHelper dbHelper;

    public ItemTemplateDB() {
        dbHelper = SQLiteHelper.getSingleton();
        if (!isTableCreated) {
            createTable();
            isTableCreated = true;
        }
    }

    public void createTable() {
        String createTable = "CREATE TABLE IF NOT EXISTS item_templates (\n"
                + "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
                + "    name TEXT NOT NULL,\n"
                + "    description TEXT,\n"
                + "    imageBase64 TEXT,\n"
                + "    type TEXT NOT NULL,\n"
                + "    level INTEGER DEFAULT 0,\n"
                + "    maxStack INTEGER DEFAULT 1,\n"
                + "    tradable BOOLEAN DEFAULT 1,\n"
                + "    destroyable BOOLEAN DEFAULT 1,\n"
                + "    sellable BOOLEAN DEFAULT 1,\n"
                + "    useLimit INTEGER DEFAULT 0,\n"
                + "    gather BOOLEAN DEFAULT 0,\n"
                + "    attributes TEXT\n"
                + ");";
        dbHelper.createTable(createTable);
    }

    public void insert(ItemTemplate template) {
        String sql = "INSERT INTO item_templates(name, description, imageBase64, type, level, maxStack, "
                + "tradable, destroyable, sellable, useLimit, gather, attributes) "
                + "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        Object[] params = {
                template.getName(),
                template.getDescription(),
                template.getImageBase64(),
                template.getType().name(),
                template.getLevel(),
                template.getMaxStack(),
                template.isTradable(),
                template.isDestroyable(),
                template.isSellable(),
                template.getUseLimit(),
                template.isGather(),
                template.getAttributes() != null && !template.getAttributes().isEmpty()
                        ? JSON.toJSONString(template.getAttributes())
                        : null
        };

        // 获取生成的自增ID并设置回实例对象
        long generatedId = dbHelper.executeUpdate(sql, params);
        if (generatedId != -1) {
            template.setId((int) generatedId);
        }
    }

    public void update(ItemTemplate template) {
        String sql = "UPDATE item_templates SET name = ?, description = ?, imageBase64 = ?, type = ?, level = ?, "
                + "maxStack = ?, tradable = ?, destroyable = ?, sellable = ?, "
                + "useLimit = ?, gather = ?, attributes = ? WHERE id = ?";
        Object[] params = {
                template.getName(),
                template.getDescription(),
                template.getImageBase64(),
                template.getType().name(),
                template.getLevel(),
                template.getMaxStack(),
                template.isTradable(),
                template.isDestroyable(),
                template.isSellable(),
                template.getUseLimit(),
                template.isGather(),
                template.getAttributes() != null && !template.getAttributes().isEmpty()
                        ? JSON.toJSONString(template.getAttributes())
                        : null,
                template.getId()
        };
        dbHelper.executeUpdate(sql, params);
    }

    public void deleteById(int id) {
        String sql = "DELETE FROM item_templates WHERE id = ?";
        Object[] params = {id};
        dbHelper.executeUpdate(sql, params);
    }

    public ItemTemplate findById(int id) {
        String sql = "SELECT * FROM item_templates WHERE id = ?";
        Object[] params = {id};
        List<ItemTemplate> templates = dbHelper.executeQuery(sql, params, ItemTemplate.class);
        return templates.isEmpty() ? null : templates.get(0);
    }

    public ItemTemplate findByName(String name) {
        String sql = "SELECT * FROM item_templates WHERE name = ?";
        Object[] params = {name};
        List<ItemTemplate> templates = dbHelper.executeQuery(sql, params, ItemTemplate.class);
        return templates.isEmpty() ? null : templates.get(0);
    }

    public List<ItemTemplate> findByType(String type) {
        String sql = "SELECT * FROM item_templates WHERE type = ?";
        Object[] params = {type};
        return dbHelper.executeQuery(sql, params, ItemTemplate.class);
    }

    public List<ItemTemplate> findAll() {
        String sql = "SELECT * FROM item_templates";
        return dbHelper.executeQuery(sql, null, ItemTemplate.class);
    }

    public Map<String, Object> findAllWithPagination(int pageNum, int pageSize) {
        int offset = (pageNum - 1) * pageSize;

        String sql = "SELECT * FROM item_templates LIMIT ? OFFSET ?";
        Object[] params = {pageSize, offset};
        List<ItemTemplate> templates = dbHelper.executeQuery(sql, params, ItemTemplate.class);

        String countSql = "SELECT COUNT(*) FROM item_templates";
        List<Integer> totalRecordsList = dbHelper.executeQuery(countSql, null, Integer.class);
        int totalRecords = totalRecordsList.isEmpty() ? 0 : totalRecordsList.get(0);

        int totalPages = (int) Math.ceil((double) totalRecords / pageSize);

        Map<String, Object> result = new HashMap<>();
        result.put("templateList", templates);
        result.put("totalRecords", totalRecords);
        result.put("totalPages", totalPages);
        result.put("currentPage", pageNum);

        return result;
    }

    public void clearData() {
        dbHelper.clearData("item_templates");
    }

    public void dropTable() {
        dbHelper.dropTable("item_templates");
    }
}